package com.ivanceras.server.db;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ivanceras.server.util.DataTypeUtils;
import com.ivanceras.shared.db.IDatabase;
import com.ivanceras.shared.exception.DataTypeException;
import com.ivanceras.shared.exception.DatabaseException;


public class DB_PostgreSQL extends DB_Jdbc implements IDatabase{

	private final String DEFAULT_PORT = "5432";
	private final String DEFAULT_HOST = "127.0.0.1";//localhost

	private final String DRIVER_CLASS = "org.postgresql.Driver";

	private String dbHost;

	private String dbPort;
	private String dbUser;
	private String dbPassword;
	/**
	 * 
	 * @param dbHost
	 * @param dbPort
	 * @param dbName
	 * @param dbUser
	 * @param dbPassword
	 * @param dbSchema
	 */
	public DB_PostgreSQL(String dbHost, String dbPort, String dbName, String dbUser, String dbPassword, String dbSchema) {
		this.dbHost = dbHost==null?DEFAULT_HOST:dbHost;
		this.dbPort = dbPort==null?DEFAULT_PORT:dbPort;
		this.dbName = dbName;
		this.dbUser = dbUser;
		this.dbPassword = dbPassword;
		this.dbSchema = dbSchema;
		this.init();
	}

	/**
	 * Integer serial, BIGSERIAL for Long or BigInteger
	 * @return
	 */
	protected String getAutoIncrementColumnConstraint(){
		return "SERIAL NOT NULL";
	}

	protected String getRealTableName(String tableName) throws DatabaseException{
		String sql = 
			"SELECT "+
			" c.relname as \"Table\""+
			" FROM"+
			" pg_catalog.pg_class c"+
			" WHERE lower(c.relname) = lower(?)"+
			" AND pg_catalog.pg_table_is_visible(c.oid) " +
			" ORDER BY c.relname";
		Object[] parameters = new Object[1];
		parameters[0] = tableName;
		try {
			ResultSet rs = executeSelectSQL(sql, parameters);
			//ResultSet rs = executeSelectSQL(sql, null);
			if(rs != null){
				if(rs.next()){
					String realTableName = rs.getString(1);
					return realTableName;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DatabaseException();
		}
		return null;

	}


	private String getDBUrl(){
		return "jdbc:postgresql://"+dbHost+":"+dbPort+"/"+dbName;
	}


	/**
	 * non-static to allow multiple database connection
	 */
	public boolean init(){
		if(initialized) return true;
		try {
			if(isDriverAvailable(DRIVER_CLASS)){
				connection = getDBConnection(getDBUrl(), dbUser, dbPassword);
				System.out.println("Using database: "+dbUser+"@"+DatabaseManager.POSTGRESQL+"://"+dbHost+":"+dbPort+"/"+dbName+"."+dbSchema);

				if(connection == null){
					System.err.println("Unable to connect to Database");
					return false;
				}else{
					initialized = true;
					Statement stmt = connection.createStatement();
					return stmt.execute("SET search_path TO "+dbSchema+",public;");
				}
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
	}

	protected Columns getColumnDetails(String tableName) throws DatabaseException{
		String sql = 
			"SELECT "+
			" c.relname as \"Table\","+
			" a.attname as \"Column\","+
			" pg_catalog.format_type(a.atttypid, a.atttypmod) as \"Datatype\""+
			" FROM"+
			" pg_catalog.pg_attribute a"+
			" LEFT JOIN pg_catalog.pg_class c"+
			" ON c.oid = a.attrelid"+
			" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"+
			" WHERE lower(c.relname) = lower(?)"+
			" AND pg_catalog.pg_table_is_visible(c.oid)"+
			" AND a.attnum > 0"+
			" AND NOT a.attisdropped";
		Object[] parameters = new Object[1];
		parameters[0] = tableName;
		try {
			ResultSet rs = executeSelectSQL(sql, parameters);
			List<String> columnList = new ArrayList<String>();
			List<String> dataTypeList = new ArrayList<String>();
			if(rs != null){
				while(rs.next()){
					String columnName = rs.getString("Column");
					String dataType = rs.getString("DataType");
					columnList.add(columnName);
					try {
						dataTypeList.add(getEquivalentJavaDataType(dataType));
					} catch (DataTypeException e) {
						e.printStackTrace();
					}
				}
				Columns columns = new Columns();
				columns.setColumns(columnList.toArray(new String[columnList.size()]));
				columns.setDataTypes(dataTypeList.toArray(new String[dataTypeList.size()]));
				return columns;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public ResultSet getColumns(String tABLE_NAME) {
		DatabaseMetaData dmd;
		ResultSet rs=null;
		try {
			dmd = getMetaData();
			rs = dmd.getColumns(null, dbSchema, tABLE_NAME.toLowerCase(), null); 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//(dbName, dbSchema, tablename, "*");
		return rs;
	}


	public ResultSet getPrimaryKeys(String tABLE_NAME) {

		ResultSet rs=null;
		try {
			DatabaseMetaData dmd = getMetaData();
			rs = dmd.getPrimaryKeys(dbName, dbSchema, tABLE_NAME.toLowerCase());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	private DatabaseMetaData getMetaData() throws SQLException {
		return connection.getMetaData();
	}

	@Override
	public String[] getTableNames(String schema, String tablePattern) throws DatabaseException{
		List<String> tableNames = new ArrayList<String>();
		String sql = "SELECT tablename from pg_tables where schemaname = ? and tablename like ? order by tablename";
		Object[] parameters = new Object[2];
		parameters[0] = schema;
		parameters[1] = tablePattern;
		ResultSet rs = executeSelectSQL(sql, parameters);
		try{
			while(rs.next()){
				String table = rs.getString("tablename");
				tableNames.add(table);
			}
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new DatabaseException();
		}
		return tableNames.toArray(new String[tableNames.size()]);
	}

}
